package com.cz.mes;

import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.sax.handler.RowHandler;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class OrderGen {
    public static void main(String[] args) {

        ExcelUtil.readBySax("C:\\Users\\root\\Desktop\\生产订单1-3月.xlsx", -1, createRowHandler());
    }

    private static RowHandler createRowHandler() {
        String dbGroup = "mes-prod-master";
        return new RowHandler() {
            @Override
            public void handle(int sheetIndex, long rowIndex, List<Object> rowlist) {
                System.out.println("第"+rowIndex+"行");
                if (rowIndex == 0) {
                    return;
                }
                try {
                    Object saleOrderNoObj = String.valueOf(rowlist.get(3));
                    if (ObjectUtil.isNotNull(saleOrderNoObj)) {
                        String sql = "select quote_post_id from omsdb.erp_post_sale_contract where code = :code";
                        Map<String, Object> paramMap = MapUtil.newHashMap();
                        paramMap.put("code", saleOrderNoObj);
                        Entity entity = Db.use(dbGroup).queryOne(sql, paramMap);
                        if (entity != null) {
                            String quotePostId = entity.getStr("quote_post_id");
                            if (StrUtil.isNotBlank(quotePostId)) {
                                Map<String, Object> param = new HashMap<>();
                                param.put("quote_item_id", quotePostId);
                                String sql1 = "select sale_target_price from post_quote_sale_item where quote_item_id = :quote_item_id";
                                Entity entity1 =  Db.use(dbGroup).queryOne(sql1, param);
                                String sale_target_price = entity1.getStr("sale_target_price");
                                System.out.println("销售单号:"+saleOrderNoObj+",未税销售单价："+sale_target_price);
                            }
                        } else {
                            //System.out.println(saleOrderNoObj + " 未找到对应报价单");
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
        };
    }
}
